package UnderAccessDBclass;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Objects;
import java.util.Properties;

public class jdbcUtils {
    private static String url;
    private static String user;
    private static String password;

    static {
        try {
            //加载配置文件
            String path = Objects.requireNonNull(jdbcUtils.class.getResource("/")).getPath() + "db.properties";
            Properties p = new Properties();
            p.load(new FileInputStream(path));
            //读取配置文件信息给属性赋值
            url = p.getProperty("sql.url");
            user = p.getProperty("sql.user");
            password = p.getProperty("sql.password");
            //注册驱动程序
            Class.forName(p.getProperty("sql.driver"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     *
     * @return 连接对象
     */
    public static Connection getConnection() {
        Connection con = null;
        try {
            con = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    /**
     * 关闭数据库连接
     *
     * @param con 连接对象
     * @param pst 预处理对象
     * @param rs  数据集对象
     */
    public static void close(Connection con, PreparedStatement pst, ResultSet rs) {
        try {
            if (con != null) {
                con.close();
            }
            if (pst != null) {
                pst.close();
            }
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 增删改的通用方法
     *
     * @param sql    SQL语句
     * @param params 需要注入的参数
     * @return 是否有数据受影响
     */
    public static boolean executeUpdate(String sql, Object[] params) {
        Connection con = getConnection();
        PreparedStatement pst = null;
        int n = 0;
        if (con != null) {
            try {
                pst = con.prepareStatement(sql);
                if (pst != null) {
                    for (int i = 0; i < params.length; i++) {
                        if (params[i].getClass() == Date.class) {
                            pst.setTimestamp(i + 1, new Timestamp(((Date) params[i]).getTime()));
                        } else {
                            pst.setObject(i + 1, params[i]);
                        }
                    }
                }
                assert pst != null;
                n = pst.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                close(con, pst, null);
            }
        }
        return n > 0;
    }

    /**
     * 查询数据的方法
     *
     * @param sql    SQL语句
     * @param params 需要注入的参数
     * @return 查询结果集
     */
    public static ArrayList<Object[]> executeQuery(String sql, Object[] params) {
        //获取连接
        Connection con = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ArrayList<Object[]> list = null;
        if (con != null) {
            try {
                //获取处理器
                pstmt = con.prepareStatement(sql);
                //注入参数
                if (params != null && params.length > 0) {
                    for (int i = 0; i < params.length; i++) {
                        if (params[i].getClass() == Date.class) {
                            System.out.println(params[i]);
                            pstmt.setTimestamp(i + 1, new Timestamp(((Date) params[i]).getTime()));
                        } else {
                            pstmt.setObject(i + 1, params[i]);
                        }
                    }
                }
                //执行查询
                rs = pstmt.executeQuery();
                if (rs != null) {
                    ResultSetMetaData rsmd = rs.getMetaData();
                    int columnCount = rsmd.getColumnCount();
                    list = new ArrayList<>();
                    while (rs.next()) {
                        Object[] row = new Object[columnCount];
                        for (int i = 0; i < columnCount; i++) {
                            row[i] = rs.getObject(i + 1);
                        }
                        list.add(row);
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                //关闭数据库
                close(con, pstmt, rs);
            }
        }

        return list;
    }

    public static void main(String[] args) {
        String sql = "select * from dep";
        Object[] params = {};
        ArrayList<Object[]> list = executeQuery(sql, params);
        System.out.println(list.size());
        System.out.println(list.get(0).length);
        for (Object[] row : list) {
            for (Object o : row) {
                System.out.print(o + " ");
            }
            System.out.println();
        }
    }
}
